/**  
 * @Title:  DataSourceImpl.java   
 * @Package com.gtjy.p2p.modules.tiles.sys.impl   
 * @Description:    TODO(用一句话描述该文件做什么)   
 * @author: 吴元森 
 * @date:   2018年7月19日 下午6:27:15   
 * @version V1.0 
 * @Copyright: 2018 吴元森.
 */
package com.platform.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.stereotype.Service;

import com.google.common.collect.Maps;
import com.platform.annotation.Authentication;
import com.platform.base.ExtTree;
import com.platform.base.PageUtil;
import com.platform.base.R;
import com.platform.bean.Name;
import com.platform.bean.TableBean;
import com.platform.bean.TableFieldBean;
import com.platform.dao.DataSourceDAO;
import com.platform.dto.DataSource;
import com.platform.exception.BizException;
import com.platform.ext.datasource.DataSourceManager;
import com.platform.ext.jdbc.SqlFunction;
import com.platform.ext.mybatis.annotation.UseDataSource;
import com.platform.ext.mybatis.dialect.DialectFactory;
import com.platform.ext.mybatis.dynamic.DynamicDataSourceContextHolder;
import com.platform.ext.mybatis.mapper.SqlMapperAdapter;
import com.platform.service.IDataSource;
import com.platform.utils.IdGen;
import com.platform.utils.JDBCUtil;
import com.platform.utils.SpringContextUtil;
import com.platform.utils.StringUtils;

import cn.hutool.core.bean.BeanUtil;

/**
 * @ClassName: DataSourceImpl
 * @Description:TODO(这里用一句话描述这个类的作用)
 * @author: 吴元森
 * @date: 2018年7月19日 下午6:27:15
 * 
 * @Copyright: 2018 吴元森.
 */
@Service

public class DataSourceImpl implements IDataSource {

	@Autowired
	private DataSourceDAO<DataSource> DAO;
	
//	@Autowired
//	private SqlMapperAdapter sqlMapperAdapter;

	/**
	 * @Title: findByPaging
	 * @Description: 查询数据源（分页）
	 * @param start
	 * @param limit
	 * @param ds
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	public R<?> findByPaging(int start, int limit, DataSource ds) {
		try {
			PageUtil pageUtil = new PageUtil();
			pageUtil.setPageIndex(start);
			pageUtil.setPageSize(limit);
			pageUtil.setParams(BeanUtil.beanToMap(ds));
			List<DataSource> dsArray = DAO.selectByMapPaging(pageUtil);
			return R.success(dsArray, pageUtil.getTotal());
		} catch (BizException e) {
			return R.fail(e);
		}
	}

	@Authentication
	public R<?> findByAll(DataSource ds) {
		List<DataSource> dsArray = new ArrayList<DataSource>();
		try {
			dsArray = DAO.selectByMap(BeanUtil.beanToMap(ds));
			return R.success(dsArray);
		} catch (BizException e) {
			return R.fail(e);
		}
	}

	/**
	 * @Title: selectDataSoucesByTree
	 * @Description: 查询数据库连接定义返回树结构
	 * @return ExtTree
	 */
	@Override
	@Authentication()
	@SuppressWarnings("all")
	public R<?> selectDataSoucesByTree(Map<String, Object> params) {
		try {
			List<DataSource> dsArray = DAO.selectByMap(params);
			ExtTree tree = new ExtTree();
			List<ExtTree> child = new ArrayList<ExtTree>();
			for (DataSource ds : dsArray) {
				ExtTree treeChild = new ExtTree();
				treeChild.setId(ds.getId().toString());
				treeChild.setText(ds.getTitle());
				treeChild.setLeaf("true");
				child.add(treeChild);
			}
			// tree.setChildren(child);
			return R.success(child);
		} catch (BizException e) {
			return R.fail(e);
		}
	}

	/**
	 * @Title: deleteByCode
	 * @Description: 删除指定数据源（物理删除）
	 * @param code
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	public R<?> deleteByCode(String code) {
		try {
			DataSource ds = DAO.selectByPrimaryKey(code);
			if(DAO.deleteByPrimaryKey(code) > 0) {
				DataSourceManager.destory(ds.getUseName());
				return R.success();
			}else {
				return R.fail();
			}
		} catch (BizException e) {
			return R.fail(e);
		}
	}

	/**
	 * @Title: insertDs
	 * @Description: 插入新的数据源
	 * @param ds
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	public R<?> insertDs(DataSource ds) {
		try {
			if(DAO.insert(ds) > 0) {
				com.platform.ext.datasource.DataSource beanDs = new com.platform.ext.datasource.DataSource();
				beanDs.setDsDatabasename(ds.getDefaultSchema());
				beanDs.setDsName(ds.getUseName());
				beanDs.setDsIp(ds.getIpAddress());
				beanDs.setDsUrl(JDBCUtil.createDBLinkStr(ds.getDataBaseType(), ds.getIpAddress(), ds.getPortNumber().intValue(), ds.getDefaultSchema()));
				beanDs.setDsPort(ds.getPortNumber());
				beanDs.setDsUsername(ds.getUserName());
				beanDs.setDsPassword(ds.getPassword());
				beanDs.setRemarks(ds.getTitle());
				DataSourceManager.add2BeanFactory(beanDs);
				return R.success();
			}else {
				return R.fail();
			}
		} catch (Exception e) {
			return R.fail(e);
		}
	}

	/**
	 * @Title: modifyDs
	 * @Description: 修改数据源
	 * @param ds
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	public R<?> modifyDs(DataSource ds) {
		try {
			if(DAO.updateBySelective(ds) > 0) {
				DataSource dss = DAO.selectByPrimaryKey(ds.getId());
				DataSourceManager.destory(dss.getUseName());
				com.platform.ext.datasource.DataSource beanDs = new com.platform.ext.datasource.DataSource();
				beanDs.setDsDatabasename(dss.getDefaultSchema());
				beanDs.setDsName(dss.getUseName());
				beanDs.setDsIp(dss.getIpAddress());
				beanDs.setDsUrl(JDBCUtil.createDBLinkStr(dss.getDataBaseType(), dss.getIpAddress(), dss.getPortNumber().intValue(), dss.getDefaultSchema()));
				beanDs.setDsPort(dss.getPortNumber());
				beanDs.setDsUsername(dss.getUserName());
				beanDs.setDsPassword(dss.getPassword());
				beanDs.setRemarks(ds.getTitle());
				DataSourceManager.add2BeanFactory(beanDs);
				return R.success();
			}else {
				return R.fail();
			}
		} catch (Exception e) {
			return R.fail(e);
		}
	}

	@Override
	@Authentication()
	@UseDataSource
	public DataSource selectById(Long id) {
		try {
			return DAO.selectByPrimaryKey(id);
		} catch (BizException e) {
			return null;
		}
	}

	/**
	 * @Title: getDataBaseTableAndView
	 * @Description: 获取指定数据源下的所有表和视图
	 * @param code
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	@UseDataSource
	public R<?> getDataBaseTableAndView(Long code) {
		code = code == null ? -1L : code;// 避免初始化报错
		DataSource ds = DAO.selectByPrimaryKey(code);
		if (ds != null) {
			List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
			SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
			sqlMapperAdapter.InjectedSessionFactory((SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
			String getTableSql = sqlMapperAdapter.sqlFun.getTables(ds.getDefaultSchema());
			String getViewsSql = sqlMapperAdapter.sqlFun.getViews(ds.getDefaultSchema());
			List<Name> tableArray = sqlMapperAdapter.sqlMapper.selectList(getTableSql, ds, Name.class);
			Map<String, Object> table = Maps.newHashMap();
			table.put("id", IdGen.getUid());
			table.put("text", "表");
			table.put("leaf", false);
			List<Map<String, Object>> tableNames = new ArrayList<>();
			for (Name name : tableArray) {
				Map<String, Object> temp = Maps.newHashMap();
				temp.put("id", IdGen.getUid());
				temp.put("text", name.getName());
				temp.put("leaf", true);
				tableNames.add(temp);
			}
			table.put("children", tableNames);
			resultList.add(table);
			List<Name> viewArray = sqlMapperAdapter.sqlMapper.selectList(getViewsSql, ds, Name.class);
			Map<String, Object> views = Maps.newHashMap();
			views.put("id", IdGen.getUid());
			views.put("text", "视图");
			views.put("leaf", false);
			List<Map<String, Object>> viewsNames = new ArrayList<>();
			for (Name name : viewArray) {
				Map<String, Object> temp = Maps.newHashMap();
				temp.put("id", IdGen.getUid());
				temp.put("text", name.getName());
				temp.put("leaf", true);
				viewsNames.add(temp);
			}
			views.put("children", viewsNames);
			resultList.add(views);
			return R.success(resultList);
		} else {
			return R.success();
		}
	}

	@Override
	@Authentication()
	@UseDataSource
	public R<?> getDataBaseTableAndViewNoTree(Long code) {
		code = code == null ? -1L : code;// 避免初始化报错
		DataSource ds = DAO.selectByPrimaryKey(code);
		if (ds != null) {
			SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
			sqlMapperAdapter.InjectedSessionFactory((SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
			String getTableSql = sqlMapperAdapter.sqlFun.getTables(ds.getDefaultSchema()); 
			List<Name> tableArray = sqlMapperAdapter.sqlMapper.selectList(getTableSql, ds, Name.class);
			List<Map<String, Object>> tableNames = new ArrayList<>();
			for (Name name : tableArray) {
				Map<String, Object> temp = Maps.newHashMap();
				temp.put("id", IdGen.getUid());
				temp.put("text", name.getName());
				temp.put("leaf", true);
				tableNames.add(temp);
			}
			String getViewsSql = sqlMapperAdapter.sqlFun.getViews(ds.getDefaultSchema());
			List<Name> viewArray = sqlMapperAdapter.sqlMapper.selectList(getViewsSql, ds, Name.class);
			for (Name name : viewArray) {
				Map<String, Object> temp = Maps.newHashMap();
				temp.put("id", IdGen.getUid());
				temp.put("text", name.getName());
				temp.put("leaf", true);
				tableNames.add(temp);
			}
			return R.success(tableNames);
		} else {
			return R.success();
		}
	}

	/**
	 * @Title: executeSQL
	 * @Description: 在指定数据源下执行SQL
	 * @param code
	 * @return ExtResultUtil
	 */
	@Override
	@Authentication()
	@UseDataSource
	public R<?> getTableField(Long code, String tableName) {
		List<TableFieldBean> resultList = new ArrayList<TableFieldBean>();
		DataSource ds = DAO.selectByPrimaryKey(code);
		SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
		try {
			sqlMapperAdapter.InjectedSessionFactory((SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
			String getTableAndViewSQL =sqlMapperAdapter.sqlFun.getTableFields(tableName, ds.getDefaultSchema());
			resultList = sqlMapperAdapter.sqlMapper.selectList(getTableAndViewSQL,TableFieldBean.class);
			return R.success(resultList);
		} catch (Exception e) {
			return R.fail(e.getMessage());
		}
	}
	
	@Authentication()
	public TableBean getFields(String tablename, String code) {
		DataSource ds = DAO.selectByPrimaryKey(code);
		SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
		sqlMapperAdapter.InjectedSessionFactory(
				(SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
		String getFieldsSql = sqlMapperAdapter.sqlFun.getTable(tablename, ds.getDefaultSchema());
		TableBean tableBean = sqlMapperAdapter.sqlMapper.selectOne(getFieldsSql, TableBean.class);
		if (tableBean != null) {
			getFieldsSql = sqlMapperAdapter.sqlFun.getTableFields(tablename, ds.getDefaultSchema());
			List<TableFieldBean> tableFieldArray = sqlMapperAdapter.sqlMapper.selectList(getFieldsSql,TableFieldBean.class);
			for (TableFieldBean field : tableFieldArray) {
				field.setDbfieldtype(field.getFieldtype());
//				field.setFieldtype(sqlMapperAdapter.sqlFun.toSqlToJava(field.getFieldtype()));
				if ("PRI".equals(field.getBy1())) {
					tableBean.setPrimarykey(field.getFieldname());
				}
				if (!StringUtils.isEmpty(field.getJointable())) {
					field.setFieldrelation(SqlFunction.MANYTOMANY);
				}
			}
			tableBean.setFields(tableFieldArray);
		}
		for (TableFieldBean field : tableBean.getFields()) {
			if (StringUtils.isEmpty(field.getComments()))
				field.setComments(field.getFieldname());
		}
		return tableBean;
	}

	@Override
	@Authentication()
	@UseDataSource
	public R<?> runSQL(Long code, String sql,int start,int limit) {
		ConfigurableApplicationContext applicationContext = (ConfigurableApplicationContext) SpringContextUtil.getApplicationContext();
		DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getBeanFactory();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		try {
			DataSource ds = DAO.selectByPrimaryKey(code);
			javax.sql.DataSource dataSource = (javax.sql.DataSource) beanFactory.getBean(ds.getUseName());
			SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
			sqlMapperAdapter.InjectedSessionFactory((SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
			if (sql.toUpperCase().contains("SELECT")) {// 查询
				Connection conn = dataSource.getConnection();
				String pagingSql = DialectFactory.getLimitSQLBy(conn.getMetaData(), start, limit, sql);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				ResultSetMetaData rsm = rs.getMetaData(); // 获得列集
				int col = rsm.getColumnCount(); // 获得列的个数
				String colName[] = new String[col];
				// 取结果集中的表头名称, 放在colName数组中
				for (int i = 0; i < col; i++) {
					colName[i] = rsm.getColumnName(i + 1);
				} // End for
				rs.close();
				stmt.close();
				conn.close();
				
				StringBuilder sqlTotalSb = new StringBuilder();
				sqlTotalSb.append(" SELECT count(1) as \"TOTAL\" FROM (").append(sql).append(") temp");
				Long dataTotal = (Long)sqlMapperAdapter.sqlMapper.selectOne(sqlTotalSb.toString()).getOrDefault("TOTAL", 0);
				resultList = sqlMapperAdapter.sqlMapper.selectList(pagingSql);
				return R.success(resultList,dataTotal.intValue()).setExtra(colName);
			} else if(sql.toUpperCase().contains("DELETE")){
				return R.success(null,"受影响行数" + sqlMapperAdapter.sqlMapper.delete(sql));
			} else if(sql.toUpperCase().contains("UPDATE")) {
				return R.success(null,"受影响行数" + sqlMapperAdapter.sqlMapper.update(sql));
			} else if(sql.toUpperCase().contains("INSERT")) {
				return R.success(null,"受影响行数" + sqlMapperAdapter.sqlMapper.insert(sql));
			}else {
				return R.fail("未知SQL脚本");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return R.fail(e.getMessage());
		}
	}

	@Override
	@Authentication()
	public R<?> executeSQL(Long code, String sql, String tableName,int start ,int limit) {
		String runSQL = "";
		DataSource ds = DAO.selectByPrimaryKey(code);
		DynamicDataSourceContextHolder.setDataSourceKey(ds.getUseName());
		if (com.platform.utils.StringUtils.isEmpty(sql)) {
			SqlMapperAdapter sqlMapperAdapter = new SqlMapperAdapter();
			sqlMapperAdapter.InjectedSessionFactory((SqlSessionTemplate) SpringContextUtil.getBeanBySqlSessionTemplate(ds.getUseName()));
			runSQL = sqlMapperAdapter.sqlFun.getTableDataPagingSQL(tableName, start, limit);
			//Integer.parseInt(sqlMapperAdapter.sqlMapper.selectOne("SELECT count(1) as total FROM" + tableName).get("total").toString())
			return R.success(sqlMapperAdapter.sqlMapper.selectList(runSQL));
		} else {
			runSQL = sql;
			return R.success(DAO.selectBySql(runSQL));
		}
	}
}
